package com.woniuxy.utils;


import com.woniuxy.controller.form.DefectConditionForm;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.SelectProvider;

import java.util.List;

public class GetSqlUtil {

    public String getDefectByConditionSql(@Param("defectConditionForm") DefectConditionForm defectConditionForm){
        String sql="SELECT * FROM t_defect WHERE ";

        if(defectConditionForm.getProjectId()!=null){
            sql+="project_id="+defectConditionForm.getProjectId()+" AND ";
        }
        if(defectConditionForm.getDefectStateId()!=null){
            sql+="defect_state_id="+defectConditionForm.getDefectStateId()+" AND ";
        }

        if(defectConditionForm.getDefectCreatStarttime()!=null){

            sql+="defect_creattime BETWEEN '"+defectConditionForm.getDefectCreatStarttime()+"' AND '"+defectConditionForm.getDefectCreatendtime()+"'";

        }

        if(sql.endsWith(" AND ")){
            StringBuilder stringBuilder = new StringBuilder(sql);
            StringBuilder delete = stringBuilder.delete(stringBuilder.length() - 5, stringBuilder.length());
            sql=delete.toString();

        }

        sql=sql+" AND defect_delete=0 AND defect_createname_id="+defectConditionForm.getUid();
        return sql;
    }


    public String getDefectByProjectIds(@Param("projectIds") List<Integer> projectIds,@Param("stateids") List<Integer> stateids){
        String sql="SELECT * FROM t_defect WHERE defect_delete=0 ";
        if(projectIds==null||projectIds.size()==0){

        }else {
            sql+="AND project_id IN (";
            int i=1;
            for (Integer projectId : projectIds) {
                sql+=projectId;
                if(i==projectIds.size()){
                    sql+=")";
                }else {
                    sql+=",";
                }
                i++;
            }
        }
        if(stateids==null||stateids.size()==0){

        }else {
            sql=sql+" AND defect_state_id IN (";
            int j=1;
            for (Integer stateid : stateids) {
                sql+=stateid;
                if(j==stateids.size()){
                    sql+=")";
                }else {
                    sql+=",";
                }
                j++;
            }
        }

        return sql;
    }

    public String getNewDefectByConditionSql(@Param("defectConditionForm") DefectConditionForm defectConditionForm,@Param("stateids") List<Integer> stateids,@Param("projectIds") List<Integer> projectIds){
        String sql="SELECT * FROM t_defect WHERE ";


        if(defectConditionForm.getProjectId()!=null){
            sql+="project_id="+defectConditionForm.getProjectId()+" AND ";
        }


        if(defectConditionForm.getDefectCreatStarttime()!=null){

            sql+="defect_creattime BETWEEN '"+defectConditionForm.getDefectCreatStarttime()+"' AND '"+defectConditionForm.getDefectCreatendtime()+"'";

        }

        if(sql.endsWith(" AND ")){
            StringBuilder stringBuilder = new StringBuilder(sql);
            StringBuilder delete = stringBuilder.delete(stringBuilder.length() - 5, stringBuilder.length());
            sql=delete.toString();

        }

        sql=sql+" AND defect_delete=0 ";

        if(projectIds==null||projectIds.size()==0){

        }else {
            sql+="AND project_id IN (";
            int i=1;
            for (Integer projectId : projectIds) {
                sql+=projectId;
                if(i==projectIds.size()){
                    sql+=")";
                }else {
                    sql+=",";
                }
                i++;
            }
        }
        if(stateids==null||stateids.size()==0){

        }else {
            sql=sql+" AND defect_state_id IN (";
            int j=1;
            for (Integer stateid : stateids) {
                sql+=stateid;
                if(j==stateids.size()){
                    sql+=")";
                }else {
                    sql+=",";
                }
                j++;
            }
        }

        return sql;
    }

    public String getDefectByProjectIdsAndStateIdsSql(@Param("projectIds") List<Integer> projectIds,@Param("defectConditionForm") DefectConditionForm defectConditionForm,@Param("stateids") List<Integer> stateids){

        if (defectConditionForm.getDefectCreatStarttime()==null&&defectConditionForm.getDefectStateId()==null&&defectConditionForm.getProjectId()==null) {
            String sql="SELECT * FROM t_defect WHERE defect_delete=0 AND defect_choice_id IS NOT NULL AND project_id IN (";
            int i=1;
            for (Integer projectId : projectIds) {
                sql+=projectId;
                if(i==projectIds.size()){
                    sql+=")";
                }else {
                    sql+=",";
                }
                i++;
            }
            if(stateids==null||stateids.size()==0){
                return sql;
            }else {
                sql=sql+" AND defect_state_id IN (";
                int j=1;
                for (Integer stateid : stateids) {
                    sql+=stateid;
                    if(j==stateids.size()){
                        sql+=")";
                    }else {
                        sql+=",";
                    }
                    j++;
                }
                sql=sql+" ORDER BY defect_changetime DESC";
                return sql;
            }

        }else {
            String sql="SELECT * FROM t_defect WHERE ";

            if(defectConditionForm.getProjectId()!=null){
                sql+="project_id="+defectConditionForm.getProjectId()+" AND ";
            }


            if(defectConditionForm.getDefectCreatStarttime()!=null){

                sql+="defect_creattime BETWEEN '"+defectConditionForm.getDefectCreatStarttime()+"' AND '"+defectConditionForm.getDefectCreatendtime()+"'";

            }

            if(sql.endsWith(" AND ")){
                StringBuilder stringBuilder = new StringBuilder(sql);
                StringBuilder delete = stringBuilder.delete(stringBuilder.length() - 5, stringBuilder.length());
                sql=delete.toString();

            }

            sql=sql+" AND defect_delete=0 AND defect_choice_id IS NOT NULL AND project_id IN (";

            int i=1;
            for (Integer projectId : projectIds) {
                sql+=projectId;
                if(i==projectIds.size()){
                    sql+=")";
                }else {
                    sql+=",";
                }
                i++;
            }


            if(stateids==null||stateids.size()==0){
                return sql;
            }else {
                sql=sql+" AND defect_state_id IN (";
                int j=1;
                for (Integer stateid : stateids) {
                    sql+=stateid;
                    if(j==stateids.size()){
                        sql+=")";
                    }else {
                        sql+=",";
                    }
                    j++;
                }
                sql=sql+" ORDER BY defect_changetime DESC";
                return sql;
            }

        }


    }


    public String getHaveMyProjectSql(@Param("projectIds") List<Integer> projectIds){
        String sql="SELECT project_id,project_name FROM t_project WHERE project_id IN (";

        int i=1;
        for (Integer projectId : projectIds) {
            sql+=projectId;
            if(i==projectIds.size()){
                sql+=")";
            }else {
                sql+=",";
            }
            i++;
        }
        sql+=" AND project_state=1";
        return sql;
    }

    public String getIsnotProject(@Param("s") String s){
        String sql = "UPDATE t_user SET u_state = u_state+1 WHERE u_id in ("+s+") AND u_delect=0";
        return sql;
    }

    public String insertProjectRoleUser(@Param("pru") String pru){
        String sql = "INSERT INTO project_role_user VALUES "+pru;
        return sql;
    }

    public String getUidAndRole(@Param("s") String s){
        String sql = "SELECT\n" +
                " tur.u_id u_id,\n" +
                " ar.role_id role_id\n" +
                "FROM t_user tur \n" +
                "JOIN account_role ar ON ar.account_id = tur.account_id\n" +
                "WHERE tur.u_id in ("+s+")";
        return sql;
    }
}
